﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TrainingWorkshop.IDAL;
using System.Data.SqlClient;
using System.Data;
using TrainingWorkshop.Model;

namespace TrainingWorkshop.DAL_SQL
{
   public  class CourseListRepository_Sql:ICourseListRepository
    {
       private System.Data.SqlClient.SqlConnection sqlConnection;

       public CourseListRepository_Sql(System.Data.SqlClient.SqlConnection sqlConnection)
       {
           // TODO: Complete member initialization
           this.sqlConnection = sqlConnection;
       }
        public TResualt Select<TResualt, T>(T t)
        {
            TResualt result=default(TResualt);

            using (this.sqlConnection) { 
                if(t==null)//select all
                {
                sqlConnection.Open();
             //   string selectStatement="select" from ;

                }
            
            }
            return result;
        }


        public List<Model.CourseList> SelectAllCourses(Condition condition)
        {
             List<CourseList> courseList = new List<CourseList>();
            using(this.sqlConnection){
                sqlConnection.Open();
               

                //prepare sql statement
                string selectStatement="SELECT CourseDetails.CourseId,Course.CourseName,Course.CourseStatus,Teacher.TeacherName,Level.LevelName,  COUNT(CourseDetails.CourseId) AS StudentNumber";
                selectStatement+=" FROM Course INNER JOIN CourseDetails ON Course.CourseId = CourseDetails.CourseId ";
                selectStatement+="INNER JOIN Level ON Course.LevelId = Level.LevelId ";
                selectStatement+="INNER JOIN Teacher ON Course.TeacherId = Teacher.TeacherId ";
                selectStatement += " where 1=1 ";

                //judge condition
                if (condition.CourseStatus >= 0)
                {
                    selectStatement += " and CourseStatus=@CourseStatus";
                }
                if (condition.TeacherName.Length > 0)
                {
                    selectStatement += " and TeacherName=@TeacherName";
                }
                if (condition.LevelName.Length > 0)
                {
                    selectStatement += " and LevelName=@LevelName";
                }
                if (condition.CourseName.Length > 0)
                {
                    selectStatement += " and CourseName like @CourseName";

                }
                
                selectStatement+=" GROUP BY CourseDetails.CourseId, Course.CourseName, Teacher.TeacherName, Level.LevelName, Course.CourseStatus ";


                //excute sql statement
                SqlDataAdapter da = new SqlDataAdapter(selectStatement, sqlConnection);
                da.SelectCommand.Parameters.AddWithValue("@CourseName", condition.CourseName);
                da.SelectCommand.Parameters.AddWithValue("@CourseStatus", condition.CourseStatus);
                da.SelectCommand.Parameters.AddWithValue("@TeacherName", condition.TeacherName);
                da.SelectCommand.Parameters.AddWithValue("@LevelName", condition.LevelName);
                
                DataTable dt = new DataTable();
                da.Fill(dt);
                //convert to list
                foreach (DataRow row in dt.Rows)
                {
                    CourseList cl = new CourseList();
                    cl.CourseId = Convert.ToInt32(row[0]);
                    cl.CourseName = row[1].ToString().Trim();
                    cl.CourseStatus = Convert.ToBoolean(row[2]);
                    cl.TeacherName = row[3].ToString().Trim();
                    cl.LevelName = row[4].ToString().Trim();
                    cl.StudentNumber = Convert.ToInt32(row[5]);
                    courseList.Add(cl);
                }
            }
            return courseList;
        }

        
    }
}
